// .........................................................................
// Title: consolidate_holdings.do
//
// Merges ownership data from insurance and investment funds holdings data
// to generate a consolidated panel for analysis
// .........................................................................

* ----------------------------------------------------
* Subset of morningstar and insurer positions in trace
* ----------------------------------------------------

* run fund import
use "$tmp/morningstar_summary/mns_security_summary_q", clear
mmerge cusip using "$tmp/trace_clean_catalog", unmatched(m)
drop _merge
replace in_trace_universe = 0 if missing(in_trace_universe)
keep if in_trace_universe == 1
drop in_trace_universe
save "$tmp/mns_security_summary_q_trace_universe", replace

* raw insurer import
use "$insurance_holdings/all_insurance_master", clear
mmerge cusip using "$tmp/trace_clean_catalog", unmatched(m)
drop _merge
replace in_trace_universe = 0 if missing(in_trace_universe)
keep if in_trace_universe == 1
drop in_trace_universe
save "$tmp/sp_q_trace_universe", replace
        
* --------------------------------------
* Collapse over insurers
* --------------------------------------

use "$tmp/sp_q_trace_universe", clear
rename entity_name holder_name
gen marketvalue_usd_noaig = marketvalue_usd if ~inlist(holder_name, "AIG (SNL Life Group)", "AIG (SNL P&C Group)")
gen aggregate_par_value_noaig = aggregate_par_value if ~inlist(holder_name, "AIG (SNL Life Group)", "AIG (SNL P&C Group)")
gcollapse (sum) marketvalue_usd marketvalue_usd_noaig aggregate_par_value aggregate_par_value_noaig, by(date_q cusip insurer_type)

gen ins_category = "ins_" + lower(insurer_type)
replace ins_category = "ins_pc" if ins_category == "ins_p&c"
assert inlist(ins_category, "ins_life", "ins_health", "ins_pc")

rename marketvalue_usd holdings_
replace holdings_ = holdings_ / 1e6
rename aggregate_par_value par_
replace par_ = par_ / 1e3

rename marketvalue_usd_noaig noaig_holdings_
replace noaig_holdings_ = noaig_holdings_ / 1e6
rename aggregate_par_value_noaig noaig_par_
replace noaig_par_ = noaig_par_ / 1e3

keep date_q cusip ins_category holdings_ par_ noaig_holdings_ noaig_par_
reshape wide holdings_ par_ noaig_holdings_ noaig_par_, i(date_q cusip) j(ins_category) string
foreach var of varlist holdings* par* noaig* {
    replace `var' = 0 if missing(`var')
}
save "$tmp/sp_q_trace_universe_summary", replace

* --------------------------------------
* Par values to market
* --------------------------------------

* convert par to market
use "$tmp/sp_q_trace_universe_summary", clear
mmerge cusip date_q using "$tmp/trace_clean_prices_q", unmatched(m) ukeep(trace_median_price_w)
drop _merge
foreach x in "life" "health" "pc"  {    
    gen holdings_pm_ins_`x' = par_ins_`x' * (trace_median_price_w / 100)
    replace holdings_pm_ins_`x' = holdings_ins_`x' if missing(holdings_pm_ins_`x')
    gen noaig_holdings_pm_ins_`x' = noaig_par_ins_`x' * (trace_median_price_w / 100)
    replace noaig_holdings_pm_ins_`x' = noaig_holdings_ins_`x' if missing(holdings_pm_ins_`x')
}
drop par_* trace_median_price_w noaig_par_*
save "$tmp/sp_q_trace_universe_summary_pm", replace

* --------------------------------------
* Reshape fund holdings
* --------------------------------------

* reshape funds
use "$tmp/mns_security_summary_q_trace_universe", clear
replace DomicileCountryId = "foreign" if Domicile != "USA"
replace DomicileCountryId = "us" if Domicile == "USA"
gcollapse (sum) marketvalue_usd, by(cusip date_q DomicileCountryId fundtype_mstar)

replace marketvalue_usd = marketvalue_usd / 1e6
rename marketvalue_usd holdings_mf_
gen fund_category = ""
replace fund_category = "us_fo" if DomicileCountryId == "us" & fundtype_mstar == "FO"
replace fund_category = "foreign_fo" if DomicileCountryId == "foreign" & fundtype_mstar == "FO"
replace fund_category = "us_fe" if DomicileCountryId == "us" & fundtype_mstar == "FE"
replace fund_category = "foreign_fe" if DomicileCountryId == "foreign" & fundtype_mstar == "FE"
replace fund_category = "us_fm" if DomicileCountryId == "us" & fundtype_mstar == "FM"
replace fund_category = "foreign_fm" if DomicileCountryId == "foreign" & fundtype_mstar == "FM"

keep cusip date_q fund_category holdings_mf_
reshape wide holdings_mf_, i(cusip date_q) j(fund_category) string
foreach var of varlist holdings* {
    replace `var' = 0 if missing(`var')
}

gen holdings_mf_us = holdings_mf_us_fo + holdings_mf_us_fe + holdings_mf_us_fm
gen holdings_mf_foreign = holdings_mf_foreign_fo + holdings_mf_foreign_fe + holdings_mf_foreign_fm
gen holdings_mf_fo = holdings_mf_us_fo + holdings_mf_foreign_fo
gen holdings_mf_fe = holdings_mf_us_fe + holdings_mf_foreign_fe
gen holdings_mf_fm = holdings_mf_us_fm + holdings_mf_foreign_fm
save "$tmp/mns_security_summary_q_trace_universe_wide", replace

* --------------------------------------
* Consolidate insurance and funds
* --------------------------------------

* merge funds and insurance, fill in zero holdings
use "$tmp/mns_security_summary_q_trace_universe_wide", clear
mmerge cusip date_q using "$tmp/sp_q_trace_universe_summary_pm", unmatched(b)
drop _merge
foreach var of varlist holdings* noaig* {
    replace `var' = 0 if missing(`var')
}

* merge in CMNS aggregation file
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation.dta", unmatched(m)
drop _merge *source*

* merge in the security masterfile info
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m)
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m) ukeep(iq_offer_date) uname(_c_)
replace issuance_date = _c_iq_offer_date if missing(issuance_date)
drop _c_iq_offer_date _merge

* save the refined sample
save "$tmp/holdings_mf_ins_trace_universe_refined", replace

* --------------------------------------
* Compute holdings shares
* --------------------------------------

* merge holdings and values outstanding; compute shares
use "$tmp/holdings_mf_ins_trace_universe_refined", clear
gen year = year(dofq(date_q))

* add in values outstanding
mmerge cusip year using "$tmp/amounts_outstanding_y_mv", unmatched(m)
drop _merge
drop if missing(value_outstanding_mv)

* holdings totals
gen holdings_mf = holdings_mf_foreign + holdings_mf_us
gen holdings_ins = holdings_pm_ins_life + holdings_pm_ins_health + holdings_pm_ins_pc
gen noaig_holdings_ins = noaig_holdings_pm_ins_life + noaig_holdings_pm_ins_health + noaig_holdings_pm_ins_pc
gen tot_holdings = holdings_mf + holdings_ins

* shares
gen share_mf_holdings = holdings_mf / (holdings_mf + holdings_ins)
gen share_ins_holdings = holdings_ins / (holdings_mf + holdings_ins)
gen share_ins_holdings_noaig = noaig_holdings_ins / (holdings_mf + holdings_ins)
gen share_mf_issuance = holdings_mf / value_outstanding_mv
gen share_ins_issuance = holdings_ins / value_outstanding_mv
gen share_ins_issuance_noaig = noaig_holdings_ins / value_outstanding_mv
gen share_pc_issuance = holdings_pm_ins_pc / value_outstanding_mv
gen share_life_issuance = holdings_pm_ins_life / value_outstanding_mv
gen share_health_issuance = holdings_pm_ins_health / value_outstanding_mv
gen holdings_by_issuance = (holdings_mf + holdings_ins) / value_outstanding_mv

* coverage ratios
gsort cusip
by cusip: egen max_holdings_ratio = max(holdings_by_issuance)
by cusip: egen min_holdings_ratio = min(holdings_by_issuance)

* drop bonds with negative total holdings
drop if min_holdings_ratio < $min_holdings_ratio

* filter out amounts outstanding that are too small relative to holdings
replace share_mf_issuance = . if max_holdings_ratio > $max_holdings_ratio
replace share_ins_issuance = . if max_holdings_ratio > $max_holdings_ratio
replace share_life_issuance = . if max_holdings_ratio > $max_holdings_ratio
replace share_health_issuance = . if max_holdings_ratio > $max_holdings_ratio
replace share_pc_issuance = . if max_holdings_ratio > $max_holdings_ratio

save "$tmp/holdings_mf_ins_issuance_trace_universe_refined", replace

* --------------------------------------
* Positions prior to event windows
* --------------------------------------

* ex-ante positions: great recession
use "$tmp/holdings_mf_ins_issuance_trace_universe_refined", clear
keep if date_q == $ex_ante_positions
drop if share_ins_holdings < 0 | share_mf_holdings < 0
keep cusip holdings_mf_foreign issuer_name cgs_domicile cusip6_up_bg country_bg issuer_name_up ///
    value_outstanding share_ins_holdings share_pc_issuance share_life_issuance share_health_issuance share_mf_issuance ///
    share_ins_issuance holdings_by_issuance tot_holdings value_outstanding_mv class_code2 share_*_noaig
save "$tmp/ex_ante_positions_great_recession", replace

* ex-ante positions: covid
use "$tmp/holdings_mf_ins_issuance_trace_universe_refined", clear
keep if date_q == $ex_ante_positions_covid
drop if share_ins_holdings < 0 | share_mf_holdings < 0
keep cusip holdings_mf_foreign issuer_name cgs_domicile cusip6_up_bg country_bg issuer_name_up ///
    value_outstanding share_ins_holdings share_pc_issuance share_life_issuance share_health_issuance share_mf_issuance ///
    share_ins_issuance holdings_by_issuance tot_holdings value_outstanding_mv class_code2 share_*_noaig
save "$tmp/ex_ante_positions_covid", replace

* ex-ante positions: p16
use "$tmp/holdings_mf_ins_issuance_trace_universe_refined", clear
keep if date_q == $ex_ante_positions_p16
drop if share_ins_holdings < 0 | share_mf_holdings < 0
keep cusip holdings_mf_foreign issuer_name cgs_domicile cusip6_up_bg country_bg issuer_name_up ///
    value_outstanding share_ins_holdings share_pc_issuance share_life_issuance share_health_issuance share_mf_issuance ///
    share_ins_issuance holdings_by_issuance tot_holdings value_outstanding_mv class_code2 share_*_noaig
save "$tmp/ex_ante_positions_p16", replace

* ex-ante positions: p11
use "$tmp/holdings_mf_ins_issuance_trace_universe_refined", clear
keep if date_q == $ex_ante_positions_p11
drop if share_ins_holdings < 0 | share_mf_holdings < 0
keep cusip holdings_mf_foreign issuer_name cgs_domicile cusip6_up_bg country_bg issuer_name_up ///
    value_outstanding share_ins_holdings share_pc_issuance share_life_issuance share_health_issuance share_mf_issuance ///
    share_ins_issuance holdings_by_issuance tot_holdings value_outstanding_mv class_code2 share_*_noaig
save "$tmp/ex_ante_positions_p11", replace
